package com.news.db;

import com.news.model.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class OpDB {
    /* *
     * 添加新闻类别

     public boolean OpAddType(String type_name){
     String sql = "insert into tb_type(type_name) values(?)";
     Object[] params = {type_name};
     DB db = new DB();
     db.doPstm(sql,params);
     return (db.getCounts() > 0)?(true):(false);
     }
     */

    /**
     * 判断数据库是否有用户的名字
     */
    public boolean OpGetUsername(String name) {
        String sql = "select * from tb_user where user_name=?";
        Object[] params = {name};
        DB db = new DB();
        db.doPstm(sql, params);
        ResultSet res = db.getRes();
        boolean b = false;
        try {
            b = res.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return b;
    }

    /**
     * 保存用户
     */
    public Boolean OpAddUser(String username, String password) {
        String sql = "insert into tb_user(user_name,user_password,user_type) values(?,?,?)";
        Object[] params = {username, password, "1"};
        DB db = new DB();
        db.doPstm(sql, params);
        return (db.getCounts() > 0) ? (true) : (false);
    }


    /**
     * 添加新闻标题/新闻/人物/管理员
     */
    public boolean OpAddObject(Object obj) {
        String sql = "";
        Object[] params = null;
        if (obj != null) {
            if (obj instanceof Type) {
                sql = "insert into tb_type(type_name) values(?)";
                Type t = (Type) obj;
                params = new Object[]{t.getType_name()};
            } else if (obj instanceof News) {
                sql = "insert into tb_info(info_type,info_title,info_content"
                        + ",info_creator) values (?,?,?,?)";
                News n = (News) obj;
                params = new Object[]{n.getInfo_type(), n.getInfo_title(), n.getInfo_content(), n.getInfo_creator()};
            } else if (obj instanceof Person) {
                sql = "insert into tb_person(people_name,people_address,people_age"
                        + ",people_job,people_remark,people_number) values (?,?,?,?,?,?)";
                Person p = (Person) obj;
                params = new Object[]{p.getPeople_name(), p.getPeople_address(), p.getPeople_age()
                        , p.getPeople_job(), p.getPeople_remark(), p.getPeople_number()};
            } else if (obj instanceof User) {
                sql = "insert into tb_user(user_name,user_password,user_type) values(?,?,?)";
                User u = (User) obj;
                params = new Object[]{u.getUser_name(), u.getUser_password(), u.getUser_type()};
            }
            DB db = new DB();
            db.doPstm(sql, params);
            return (db.getCounts() > 0) ? (true) : (false);
        } else {
            return false;
        }

    }

    /**
     * 获取所有新闻类别
     */
    public ArrayList<Type> OpGetType() {
        ArrayList<Type> list = new ArrayList<>();
        String sql = "select * from tb_type";
        DB db = new DB();
        db.doPstm(sql, null);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                Type t = new Type();
                t.setId(res.getInt("id"));
                t.setType_name(res.getString("type_name"));
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
        }
        return list;
    }

    /**
     * 获取最新的三个新闻人物
     */
    public ArrayList<Person> OpGetPerson() {
        ArrayList<Person> list = new ArrayList<>();
        String sql = "select TOP 3 * from tb_person order by people_time desc";
        DB db = new DB();
        db.doPstm(sql, null);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                Person p = new Person();
                p.setId(res.getInt("id"));
                p.setPeople_address(res.getString("people_address"));
                p.setPeople_age(res.getInt("people_age"));
                p.setPeople_job(res.getString("people_job"));
                p.setPeople_number(res.getInt("people_number"));
                p.setPeople_name(res.getString("people_name"));
                p.setPeople_remark(res.getString("people_remark"));
                p.setPeople_time(res.getDate("people_time"));
                list.add(p);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
        }

        return list;
    }

    /**
     * 根据ID获取指定的新闻人物
     */
    public Person OpGetPerson(int id) {
        Person p = new Person();
        String sql = "select * from tb_person where id=?";
        Object[] params = {id};
        DB db = new DB();
        db.doPstm(sql, params);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                p.setId(res.getInt("id"));
                p.setPeople_address(res.getString("people_address"));
                p.setPeople_age(res.getInt("people_age"));
                p.setPeople_job(res.getString("people_job"));
                p.setPeople_number(res.getInt("people_number"));
                p.setPeople_name(res.getString("people_name"));
                p.setPeople_remark(res.getString("people_remark"));
                p.setPeople_time(res.getDate("people_time"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            p = null;
        }
        return p;
    }

    /**
     * 根据ID获取新闻类别名称
     */
    public String OpGetTypeName(int id) {
        String name = "";
        String sql = "select * from tb_type where id=?";
        Object[] params = {id};
        DB db = new DB();
        db.doPstm(sql, params);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                Type type = new Type();
                type.setType_name(res.getString("type_name"));
                name = type.getType_name();
            }
        } catch (Exception e) {
            e.printStackTrace();
            name = "";
        }
        return name;
    }

    /**
     * 根据1d给指定的新闻人物技票
     */
    public boolean OpVotePerson(int id) {
        Person p = this.OpGetPerson(id);
        int number = p.getPeople_number() + 1;
        String sql = "update tb_person set people_number=? where id=?";
        Object[] params = {number, id};
        DB db = new DB();
        db.doPstm(sql, params);
        if (db.getCounts() > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 管理员登录
     */
    public boolean OpLogin(User user) {
        String sql = "select * from tb_user";
        ArrayList<User> list = new ArrayList<>();
        DB db = new DB();
        db.doPstm(sql, null);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                User u = new User();
                u.setId(res.getInt("id"));
                u.setUser_name(res.getString("user_name"));
                u.setUser_password(res.getString("user_password"));
                u.setUser_type(res.getString("user_type"));
                list.add(u);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
            return false;
        }
        if (list != null && list.size() > 0) {
            for (User u : list) {
                if (u.getUser_name().equals(user.getUser_name()) && u.getUser_password().equals(user.getUser_password())) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 根据类别获取前两个新闻
     */
    public ArrayList<News> OpGetNews(int info_type) {
        ArrayList<News> list = new ArrayList<News>();
        String sql = "select TOP 2 * from tb_info where info_type=? order by info_date desc";
        Object[] params = {info_type};
        DB db = new DB();
        db.doPstm(sql, params);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                News n = new News();
                n.setId(res.getInt("id"));
                n.setInfo_type(res.getInt("info_type"));
                n.setInfo_title(res.getString("info_title"));
                n.setInfo_content(res.getString("info_content"));
                n.setInfo_creator(res.getString("info_creator"));
                n.setInfo_date(res.getDate("info_date"));
                n.setTypeName();
                list.add(n);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
        }
        return list;
    }

    /**
     * 获取指定类别下的所有新闻
     */
    public ArrayList<News> OpGetNews(int info_type, Object obj) {
        ArrayList<News> list = new ArrayList<News>();
        String sql = "select * from tb_info where info_type=? order by info_date desc";
        Object[] params = {info_type};
        DB db = new DB();
        db.doPstm(sql, params);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                News n = new News();
                n.setId(res.getInt("id"));
                n.setInfo_type(res.getInt("info_type"));
                n.setInfo_title(res.getString("info_title"));
                n.setInfo_content(res.getString("info_content"));
                n.setInfo_creator(res.getString("info_creator"));
                n.setInfo_date(res.getDate("info_date"));
                n.setTypeName();
                list.add(n);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
        }
        return list;
    }

    /**
     * 根据ID获取新闻
     */
    public News OpGetNewsInfo(int id) {
        News n = new News();
        String sql = "select TOP 2 * from tb_info where id=? order by info_date desc";
        Object[] params = {id};
        DB db = new DB();
        db.doPstm(sql, params);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                n.setId(res.getInt("id"));
                n.setInfo_type(res.getInt("info_type"));
                n.setInfo_title(res.getString("info_title"));
                n.setInfo_content(res.getString("info_content"));
                n.setInfo_creator(res.getString("info_creator"));
                n.setInfo_date(res.getDate("info_date"));
                n.setTypeName();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return n;
    }

    /* *
     * 获取所有的新闻记录
     */
    public ArrayList<News> OpGetNewsAll(String sql) {
        ArrayList<News> list = new ArrayList<>();
        DB db = new DB();
        db.doPstm(sql, null);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                News n = new News();
                n.setId(res.getInt("id"));
                n.setInfo_type(res.getInt("info_type"));
                n.setInfo_title(res.getString("info_title"));
                n.setInfo_content(res.getString("info_content"));
                n.setInfo_creator(res.getString("info_creator"));
                n.setInfo_date(res.getDate("info_date"));
                n.setTypeName();
                list.add(n);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
        }
        return list;
    }

    /* *
     * 数据分页
     */
    public CreatePage OpCreatePage(String sql, Object[] params, int perP, String gowhich, String showpage) {
        CreatePage page = new CreatePage();
        DB db = new DB();
        db.doPstm(sql, params);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                res.last();
                page.setAllR(res.getRow());
                page.setPerP(perP);
                page.setAllP();
                page.setCurrentP(showpage);
                page.setPageInfo();
                page.setPageLink(gowhich);
            }
        } catch (Exception e) {
            e.printStackTrace();
            page = null;
        }
        return page;
    }

    /* *
     * 根据id删除新闻
     */
    public void delNews(String idstr) {
        int id = Integer.parseInt(idstr);
        String sql = "delete tb_Info where id=?";
        Object[] params = {id};
        DB db = new DB();
        db.doPstm(sql, params);
    }

    /**
     * 修改新闻/人物
     */
    public boolean OpEditObject(Object obj) {
        String sql = "";
        Object[] params = null;
        if (obj != null) {
            if (obj instanceof News) {
                sql = "update tb_info set info_type=?,info_title=?,info_content=?"
                        + ",info_creator=? where id = ?";
                News n = (News) obj;
                params = new Object[]{n.getInfo_type(), n.getInfo_title(), n.getInfo_content(), n.getInfo_creator(), n.getId()};
            } else if (obj instanceof Person) {
                sql = "update tb_Person set people_name=?,people_address=?,people_age=?,people_job=?,people_remark=?,people_number=? where id = ?";
                Person p = (Person) obj;
                params = new Object[]{p.getPeople_name(), p.getPeople_address(), p.getPeople_age(), p.getPeople_job(), p.getPeople_remark(), p.getPeople_number(), p.getId()};
            }
            DB db = new DB();
            db.doPstm(sql, params);
            return (db.getCounts() > 0) ? (true) : (false);
        } else {
            return false;
        }
    }

    /**
     * 查询人物
     */
    public ArrayList<Person> OpGetPersons(int rows, int start) {
        ArrayList<Person> list = new ArrayList<Person>();
        //m查询的起始行 n查询的末行
        //select top (n-m+1) * from tb_person where id not in (  select top m-1 id from tb_person)
        String sql = "select top " + rows + " * from tb_person where id not in (select top " + start + " id from tb_person)";
        DB db = new DB();
        db.doPstm(sql, null);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                Person p = new Person();
                p.setId(res.getInt("id"));
                p.setPeople_name(res.getString("people_name"));
                p.setPeople_address(res.getString("people_address"));
                p.setPeople_job(res.getString("people_job"));
                p.setPeople_remark(res.getString("people_remark"));
                p.setPeople_number(res.getInt("people_number"));
                p.setPeople_time(res.getDate("people_time"));
                list.add(p);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
        }
        return list;
    }

    /* *
     * 获取所有新闻人物数目
     */
    public int OpCountPerson() {
        ArrayList<Person> list = new ArrayList<Person>();
        String sql = "select * from tb_person";
        DB db = new DB();
        db.doPstm(sql, null);
        ResultSet res = db.getRes();
        int i = 0;
        try {
            while (res.next()) {
                Person p = new Person();
                p.setId(res.getInt("id"));
                p.setPeople_name(res.getString("people_name"));
                p.setPeople_address(res.getString("people_address"));
                p.setPeople_job(res.getString("people_job"));
                p.setPeople_remark(res.getString("people_remark"));
                p.setPeople_number(res.getInt("people_number"));
                p.setPeople_time(res.getDate("people_time"));
                list.add(p);
                i = list.size();
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
        }
        return i;
    }

    /**
     * 根据id删除新闻人物
     */
    public void delPerson(String idstr) {
        int id = Integer.parseInt(idstr);
        String sql = "delete tb_person where id=?";
        Object[] params = {id};
        DB db = new DB();
        db.doPstm(sql, params);
    }

    /**
     * 模糊查找新闻
     */
    public ArrayList<News> OpSearchNews(String search) {
        ArrayList<News> list = new ArrayList<News>();
        String sql = "select * from tb_info where info_content  like ? or info_title  like ? or info_creator  like ?";
        Object[] params = {"%" + search + "%", "%" + search + "%", "%" + search + "%"};
        DB db = new DB();
        db.doPstm(sql, params);
        ResultSet res = db.getRes();
        try {
            while (res.next()) {
                News n = new News();
                n.setId(res.getInt("id"));
                n.setInfo_type(res.getInt("info_type"));
                n.setInfo_title(res.getString("info_title"));
                n.setInfo_content(res.getString("info_content"));
                n.setInfo_creator(res.getString("info_creator"));
                n.setInfo_date(res.getDate("info_date"));
                n.setTypeName();
                list.add(n);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list = null;
        }
        return list;
    }
}
